﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using CNCSoft.ErpApps.TMS.myclass;
using Common;
using System.Data.SqlClient;
using CNCSoft.ErpApps.TMS.longGiaoVu;
using CNCSoft.EData;

namespace CNCSoft.ErpApps.TMS
{
    public partial class frmDangKyGiaoVienLop : Form
    {
        CommonClass mine;
        SqlConnection conn;
        DataSet ds;
        string _mangaytrongtuan, _matimeslot, _magiaovien, _makhoahoc;
        double _rateusd = 0; double _ratevnd = 0;

        bool _insert = false;
        bool _edit = false;
        bool _dangday = false;
        frmDangKyGiaoVienLopAddition frm = null;

        public frmDangKyGiaoVienLop()
        {
            InitializeComponent();
        }

        private void frmDangKyGiaoVienLop_Load(object sender, EventArgs e)
        {
            conn = new SqlConnection(GlobalVariable.WSInfo.ConnectionString);
            ds = new DataSet();
            mine = new CommonClass();
            LoadControl();
        }

        private void searchLookUpEditLop_EditValueChanged(object sender, EventArgs e)
        {
            try 
	        {	        
		        string makhoahoc = searchLookUpEditLop.EditValue.ToString();
                TeacherOfClass(makhoahoc);
                if (ds.Tables["TableTeacherOfClass"].Rows.Count > 0)
                {
                    lblTenGiaoVien.Text = ds.Tables["TableTeacherOfClass"].Rows[0]["TenGiaoVien"].ToString();
                }
                else
                {
                    lblTenGiaoVien.Text = "- - -";
                }
                LoadTKBGioHoc(makhoahoc);
                lblThoiKhoaBieu.Text = ds.Tables["TableInfoKhoaHoc"].Rows[0]["TenNgayTrongTuan"].ToString() + " - " + ds.Tables["TableInfoKhoaHoc"].Rows[0]["TenGiohoc"].ToString();
                _mangaytrongtuan = ds.Tables["TableInfoKhoaHoc"].Rows[0]["MaNgayTrongTuan"].ToString();
                _matimeslot = ds.Tables["TableInfoKhoaHoc"].Rows[0]["MaTimeSlot"].ToString();

                SearchTeacherByDayInWeekAndTimeSlot(_mangaytrongtuan, _matimeslot);
                gridControlGiaoVien.DataSource = ds.Tables["TableFiltTeacher"];
	        }
	        catch (Exception ex)
	        {
                MessageBox.Show(ex.Message);
	        }
        }

        private void simpleButtonDangKy_Click(object sender, EventArgs e)
        {
            try
            {
                if (searchLookUpEditLop.EditValue != null)
                {
                    _insert = true;
                    _edit = false;
                    frm = new frmDangKyGiaoVienLopAddition(searchLookUpEditLop.EditValue.ToString(), _magiaovien, _insert, _edit, _dangday, _rateusd, _ratevnd);
                    frm.ShowDialog();
                    SearchTeacherByDayInWeekAndTimeSlot(_mangaytrongtuan, _matimeslot);
                    gridControlGiaoVien.DataSource = ds.Tables["TableFiltTeacher"];
                }
                else
                    MessageBox.Show("Bạn chưa chọn lớp!");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btn_AllTeacher_Click(object sender, EventArgs e)
        {
            try
            {
                LoadGiaoVien();
                gridControlGiaoVien.DataSource = ds.Tables["TableGiaoVien"];
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void gridViewGiaoVien_RowClick(object sender, DevExpress.XtraGrid.Views.Grid.RowClickEventArgs e)
        {
            try
            {
                _magiaovien = gridViewGiaoVien.GetDataRow(e.RowHandle)["MaGiaoVien"].ToString();
                _makhoahoc = gridViewGiaoVien.GetDataRow(e.RowHandle)["MaKhoaHoc"].ToString();
                if (gridViewGiaoVien.GetDataRow(e.RowHandle)["RateUSD"].ToString() != "")
                {
                    _rateusd = Convert.ToDouble(gridViewGiaoVien.GetDataRow(e.RowHandle)["RateUSD"]);
                }
                if (gridViewGiaoVien.GetDataRow(e.RowHandle)["RateVND"].ToString() != "")
                {
                    _ratevnd = Convert.ToDouble(gridViewGiaoVien.GetDataRow(e.RowHandle)["RateVND"]);
                }
                if (gridViewGiaoVien.GetDataRow(e.RowHandle)["DangDay"].ToString() != null && gridViewGiaoVien.GetDataRow(e.RowHandle)["DangDay"].ToString() != "")
                {
                    _dangday = Convert.ToBoolean(gridViewGiaoVien.GetDataRow(e.RowHandle)["DangDay"]);
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void btn_CapNhat_Click(object sender, EventArgs e)
        {
            try 
	        {
                if (_magiaovien != null && _makhoahoc != null)
                {
                    _insert = false;
                    _edit = true;
                    frm = new frmDangKyGiaoVienLopAddition(_makhoahoc, _magiaovien, _insert, _edit, _dangday, _rateusd, _ratevnd);
                    frm.ShowDialog();
                    SearchTeacherByDayInWeekAndTimeSlot(_mangaytrongtuan, _matimeslot);
                    gridControlGiaoVien.DataSource = ds.Tables["TableFiltTeacher"];
                }
	        }
	        catch (Exception ex)
	        {
                MessageBox.Show(ex.Message);
	        }
        }

        #region coding
        private void LoadControl()
        {
            try
            {
                LoadData();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadData()
        {
            try
            {
                LoadGiaoVien();
                gridControlGiaoVien.DataSource = ds.Tables["TableGiaoVien"];
                LoadKhoaHoc();
                searchLookUpEditLop.Properties.DataSource = ds.Tables["TableKhoaHoc"];
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadGiaoVien()
        {
            if(ds.Tables["TableGiaoVien"] != null)
            {
                ds.Tables["TableGiaoVien"].Clear();
            }
            string sql =
                @"SELECT 
                    dmgv.MaGiaoVien, dmgv.TenGiaoVien, dmgv.MaDonVi,
                    khgv.MaKhoaHoc, dmkh.TenKhoaHoc, khgv.RateUSD, khgv.RateVND,
                    CONVERT(VARCHAR, dmkh.NgayBatDau, 103) AS NgayBatDau, 
                    dmngay.TenNgayTrongTuan AS NgayTrongTuan, dmgh.TenGiohoc AS GioHoc,
                    dmgv.DienThoaiDiDong as DienThoai, dmgv.Email, dmgv.NgayTrangThai, dmtt.TenTrangThaiGiaoVien AS TrangThaiGiaoVien, 
                    dmgv.WorkPermit, khgv.DangDay, khgv.Note
                    FROM DM_GIAOVIEN dmgv 
                    LEFT OUTER JOIN KhoaHoc_GiaoVien khgv
                    ON khgv.MaGiaoVien = dmgv.MaGiaoVien
                    LEFT OUTER JOIN DM_KHOAHOC dmkh
                    ON dmkh.MaKhoaHoc = khgv.MaKhoaHoc
                    LEFT OUTER JOIN DM_NGAYTRONGTUAN dmngay
                    ON dmngay.MaNgayTrongTuan = dmkh.MaNgayTrongTuan
                    LEFT OUTER JOIN DM_GIOHOC dmgh
                    ON dmgh.MaGiohoc = dmkh.MaTimeSlot
                    LEFT OUTER JOIN DM_TRANGTHAIGIAOVIEN dmtt
                    ON dmtt.MaTrangThaiGiaoVien = dmgv.MaTrangThaiGiaoVien
                    WHERE dmgv.MaDonVi = '" + GlobalVariable.WSInfo.CompanyID+@"'
                    ORDER BY dmgv.MaGiaoVien, dmkh.TenKhoaHoc";
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            da.Fill(ds, "TableGiaoVien");
        }

        private void LoadKhoaHoc()
        {
            try
            {
                if (ds.Tables["TableKhoaHoc"] != null)
                    ds.Tables["TableKhoaHoc"].Clear();
                string sql = @"select * 
                                from DM_KHOAHOC dmkh
                                left outer join DM_NGAYTRONGTUAN dmtkb on dmtkb.MaNgayTrongTuan = dmkh.MaNgayTrongTuan
                                left outer join DM_GIOHOC dmgh on dmgh.MaGiohoc = dmkh.MaTimeSlot";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableKhoaHoc");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void LoadTKBGioHoc(string makhoahoc)
        {
            try
            {
                if (ds.Tables["TableInfoKhoaHoc"] != null)
                    ds.Tables["TableInfoKhoaHoc"].Clear();
                string sql = @"select dmkh.MaNgayTrongTuan, TenNgayTrongTuan, MaTimeSlot, TenGiohoc
                                from DM_KHOAHOC dmkh
                                left outer join DM_NGAYTRONGTUAN dmtkb on dmtkb.MaNgayTrongTuan = dmkh.MaNgayTrongTuan
                                left outer join DM_GIOHOC dmgh on dmgh.MaGiohoc = dmkh.MaTimeSlot
                                where MaKhoaHoc = '"+makhoahoc+"'";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableInfoKhoaHoc");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }

        private void SearchTeacherByDayInWeekAndTimeSlot(string mangaytrongtuan, string magiohoc)
        {
            try 
	        {
                if (ds.Tables["TableFiltTeacher"] != null)
                    ds.Tables["TableFiltTeacher"].Clear();
                string sql = @"SELECT 
                            dmgv.MaGiaoVien, dmgv.TenGiaoVien, dmgv.MaDonVi,
                            khgv.MaKhoaHoc, dmkh.TenKhoaHoc, khgv.RateUSD, khgv.RateVND,
                            CONVERT(VARCHAR, dmkh.NgayBatDau, 103) AS NgayBatDau, 
                            dmngay.TenNgayTrongTuan AS NgayTrongTuan, dmgh.TenGiohoc AS GioHoc,
                            dmgv.DienThoaiDiDong as DienThoai, dmgv.Email, dmgv.NgayTrangThai, dmtt.TenTrangThaiGiaoVien AS TrangThaiGiaoVien, 
                            dmgv.WorkPermit, khgv.DangDay
                            FROM DM_GIAOVIEN dmgv 
                            LEFT OUTER JOIN KhoaHoc_GiaoVien khgv
                            ON khgv.MaGiaoVien = dmgv.MaGiaoVien
                            LEFT OUTER JOIN DM_KHOAHOC dmkh
                            ON dmkh.MaKhoaHoc = khgv.MaKhoaHoc
                            LEFT OUTER JOIN DM_NGAYTRONGTUAN dmngay
                            ON dmngay.MaNgayTrongTuan = dmkh.MaNgayTrongTuan
                            LEFT OUTER JOIN DM_GIOHOC dmgh
                            ON dmgh.MaGiohoc = dmkh.MaTimeSlot
                            LEFT OUTER JOIN DM_TRANGTHAIGIAOVIEN dmtt
                            ON dmtt.MaTrangThaiGiaoVien = dmgv.MaTrangThaiGiaoVien
                            WHERE dmgv.MaDonVi = '" + GlobalVariable.WSInfo.CompanyID+@"'
                            AND 
                            (
		                        dmgv.MaGiaoVien not in
		                        (
									select khgv.MaGiaoVien
									from DM_KHOAHOC dmkh inner join KhoaHoc_GiaoVien khgv on dmkh.MaKhoaHoc = khgv.MaKhoaHoc
									WHERE
									(
										khgv.DangDay = 1
										and
										dmkh.MaNgayTrongTuan in('"+mangaytrongtuan+@"')
										and
										dmkh.MaTimeSlot in('"+magiohoc+@"')
									)
		                        )
                                OR
                                khgv.DangDay = 0
	                        )
                            ORDER BY dmgv.MaGiaoVien, dmkh.TenKhoaHoc";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableFiltTeacher");
	        }
	        catch (Exception ex)
	        {
		        MessageBox.Show(ex.Message);
	        }
        }

        private void TeacherOfClass(string makhoahoc)
        {
            try
            {
                if (ds.Tables["TableTeacherOfClass"] != null)
                    ds.Tables["TableTeacherOfClass"].Clear();
                string sql =
                @"SELECT   DM_GIAOVIEN.TenGiaoVien
                    FROM    DM_GIAOVIEN LEFT OUTER JOIN
	                        KhoaHoc_GiaoVien ON DM_GIAOVIEN.MaGiaoVien = KhoaHoc_GiaoVien.MaGiaoVien
                    WHERE   KhoaHoc_GiaoVien.MaKhoaHoc = '" + makhoahoc + @"' and KhoaHoc_GiaoVien.DangDay = 1";
                SqlDataAdapter da = new SqlDataAdapter(sql, conn);
                da.Fill(ds, "TableTeacherOfClass");
            }
            catch (Exception ex)
            {               
                MessageBox.Show(ex.Message);
            }
        }
        #endregion
    }
}